package code.excel.poi;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import jxl.read.biff.BiffException;

/**
* <p></p>
*
* @version 1.0
* @author:huangwf
* @date:2017/8/17 16:07
* @history
*/

public class ExcelHandlePoiExample {


/**
* <p>读取Excel</p>
*
* @author:huangwf
* @date:2017/8/17 15:37
* @version 1.0
* @history
*/
public static void ReadExcelPoi(String filePath, String fileName) throws IOException, BiffException, InvalidFormatException {

File xlsFile = new File(filePath + fileName);
InputStream is = new FileInputStream(xlsFile);
// 获得工作簿
Workbook workbook = WorkbookFactory.create(is); //从poi-3.7.jar开始, WorkbookFactory类已经放进poi-ooxml-XXX.jar中了.
// 获得工作表个数
int sheetCount = workbook.getNumberOfSheets();
// 遍历工作表
for (int i = 0; i < sheetCount; i++) {
Sheet sheet = workbook.getSheetAt(i);
// 获得行数
int rows = sheet.getLastRowNum() + 1;
// 获得列数，先获得一行，在得到改行列数
Row tmp = sheet.getRow(0);
if (tmp == null) {
continue;
}
int cols = tmp.getPhysicalNumberOfCells();
// 读取数据
for (int row = 0; row < rows; row++) {
Row r = sheet.getRow(row);
for (int col = 0; col < cols; col++) {
System.out.printf("%10s", r.getCell(col).getStringCellValue());
}
System.out.println();
}
}

}

/**
* <p>写入Excel</p>
*
* @author:huangwf
* @date:2017/8/17 15:37
* @version 1.0
* @history
*/
public static void wirteExcelPoi(String filePath, String fileName) throws IOException {
// 创建工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建工作表
HSSFSheet sheet = workbook.createSheet("poi");
HSSFRow newRow;
int rows = 10, cols = 10;
for (int row = 0; row < rows; row++) {
newRow = sheet.createRow(row);
for (int col = 0; col < cols; col++) {
// 向工作表中添加数据
newRow.createCell(col).setCellValue("data" + row + col);
}
}
newRow = sheet.createRow(rows);
for (int col = 0; col < cols; col++) {
newRow.createCell(col).setCellValue("data" + rows + col);
}

File xlsFile = new File(filePath + fileName);
FileOutputStream xlsStream = new FileOutputStream(xlsFile,true);
workbook.write(xlsStream);
}

public static void main(String[] args) {

try {
String path = "excel/out/excel/";
String name = "poi.xls";
wirteExcelPoi(path, name);
ReadExcelPoi(path, name);
} catch (IOException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
}
}
